Information is conveniently stored in repositories such as databases. Stored information is accessed and updated with applications executing in a database management system (DBMS). A DBMS functions to store and access information in a database in one or more data containers, each of which contains records. The data within each record is organized into one or more attribute fields. Various database architectures exist.
The data containers of relational databases are tables and the records stored therein are rows. The database can contain virtually any number of tables, the attributes of which, are referred to as columns. The data containers of an object oriented database are object classes, the records of which comprise objects, which have attributes referred to as object attributes. Other database architectures also exist. For simplicity and unity herein, a relational database is discussed, solely by way of example.
To retrieve data stored in a database, queries are submitted to a database server, which computes the query and returns the data requested therewith. Query statements submitted to a database server typically conform to syntax characteristic of a particular database language with which the DBMS functions. SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL) are well known examples of such languages. Other database languages, some of them proprietary, are also used. For simplicity and unity, and solely by way of example, SQL statements are discussed herein. Unless otherwise indicated, terms and phrases are used herein in contexts explained in the four paragraphs that follow, below.
Views can allow the results of some queries to be computed more efficiently. The term “view” refers to a predefined query that is treated as a table. A view is thus essentially a virtual table and may be referenced by a database statement as if the view were an actual table. Tables that actually store the data associated with a view are referred to as “base tables.” Base tables are referenced, directly or indirectly, by the predefined query. To generate the rows of a view, data is extracted and derived from the base tables. Changing data in a view's base table alters the data that the view shows.
Unless otherwise indicated, the term “Materialized View” (MV) refers to a view for which the results of the predefined query are stored as precomputed values. Materializing Materialized views obviates multiple execution of queries that may require significant time to compute and thus, may allow faster and more efficient access to data. Materialized views are associated with their respective base tables and views with metadata stored in detail tables. Further, materialized views allow queries to be dynamically and transparently rewritten to reference one or more Materialized views in their own base tables, which can boost database performance by allowing faster and more efficient computing of queries. Unless otherwise indicated, the term “query rewrite” herein refers to transforming a SQL query, expressed in terms of tables and views, into a statement that accesses the Materialized views associated with those tables and views, instead. Inline views may be used to facilitate some query rewrites.
Materialized views can contain inline views, which are referred to herein as materialized view inline views. The inline views in a query are referred to herein as query inline views. An inline view essentially functions as a query, itself. Unless otherwise indicated, the phrase “inline view” herein refers to a SELECT statement (e.g., within a query) that is nested inside of another SELECT statement. An inline view essentially comprises an object-like entity, e.g., a query-like object in a base table, that is referred to in the FROM clause of an outer query, and which is created by the complete SELECT statement. Treated in some respects like an object in a base table that is referred to in the FROM clause of the outer query, inline views allow simplification of query constructs and faster and more efficient query computation.
Unlike the base table, other regular tables and named views however, inline views are not true database objects per se. As a consequence, using materialized views to rewrite queries that contain inline views can pose difficulties. For instance, to use an materialized view for rewriting a query that contains an inline view, the inline views in the materialized view should textually match the inline views in a query. In practice, consistent rewriting of queries that have inline views using materialized views has proven virtually impossible to achieve because the probability that queries' inline views will always exactly match the inline views of relevant Materialized views is very low, especially for queries that are received from “third party” (e.g., with respect to the DBMS) applications.
A consistent expectation that a query's inline views will always exactly match the inline views of relevant Materialized views is thus implausible. This is born out in practice, as many, if not most queries with inline views fail to rewrite. The failure of most queries with inline views to rewrite can degrade DBMS performance. For instance, many modern database applications use both inline views and query rewrite for operational efficiency and speed.
An application that uses inline views and query rewrite will likely suffer from missed query rewrite opportunities because of the high probability that its query's inline views will fail to exactly match the inline views of the materialized views textually. When such an application misses a query rewrite opportunity, its query must be computed by accessing base tables and extracting data therefrom rather than with reference to Materialized views, which is generally less efficient than computing the query using an materialized view.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section. Similarly, issues identified with respect to one or more approaches should not assume to have been recognized in any prior art on the basis of this section, unless otherwise indicated.
Based on the foregoing, it would be useful to facilitate query rewrite using inline views.